package io.xmu.dataanalysis.repository;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import io.xmu.dataanalysis.entity.Search;

import java.util.List;

public interface SearchRepo extends PagingAndSortingRepository<Search, Integer> {

    @Query(value = " SELECT  search.`key` as keyword , COUNT(*) as counts FROM search  " +
            " WHERE SearchTime >= ?1 AND SearchTime < ?2 " +
            " GROUP BY keyword " +
            " ORDER BY counts DESC  " +
            " LIMIT 10 ",nativeQuery = true)
    List<Object[]> findSearchKeywordTop10(String start,String end);


    @Query(value = "SELECT count(*) AS counts FROM search WHERE `key`= ?1 " +
            "AND SearchTime >= ?2 AND SearchTime < ?3 ",nativeQuery = true)
    Integer findCountByKeyAndModifiedTime(String key,String start,String end);


}
